package com.aurifa.struts2.tutorial.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.aurifa.struts2.tutorial.model.Department;
import com.aurifa.struts2.tutorial.model.Employee;

public class EmployeeMySqldao implements EmployeeDao {
	private static Map departmentsMap;
	private static ArrayList employees;

	static {
		employees = new ArrayList();
		DepartmentDao deptDao = new DepartmentMySqldao();
		departmentsMap = deptDao.getDepartmentsMap();
		Department dep = null;
		Connection con = null;
		Statement s = null;
		ResultSet rs = null;
		try {
			con = MySqldao.getCon();
			s = con.createStatement();
			rs = s.executeQuery("select * from employee");
			while (rs.next()) {
				dep = (Department) departmentsMap.get(rs.getInt(5));
				employees.add(new Employee(rs.getInt(1), rs.getString(3), rs
						.getString(4), rs.getInt(2), new Department(dep
						.getDepartmentId(), dep.getName())));
			}
			MySqldao.closeRs(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MySqldao.closeRs(rs);
			MySqldao.closeS(s);
			MySqldao.closeCon(con);
		}
	}

	Log logger = LogFactory.getLog(this.getClass());

	public List getAllEmployees() {
		return employees;
	}

	public Employee getEmployee(Integer id) {
		Employee emp = null;
		Iterator iter = employees.iterator();
		while (iter.hasNext()) {
			emp = (Employee) iter.next();
			if (emp.getEmployeeId().equals(id)) {
				break;
			}
		}
		return emp;
	}

	public void update(Employee emp) {
		Integer id = emp.getEmployeeId();
		for (int i = 0; i < employees.size(); i++) {
			Employee tempEmp = (Employee) employees.get(i);
			if (tempEmp.getEmployeeId().equals(id)) {
				emp.setDepartment((Department) departmentsMap.get(emp
						.getDepartment().getDepartmentId()));
				employees.set(i, emp);

				Connection con = null;
				PreparedStatement ps = null;
				String sql = "update employee set age = ?, firstName = ?, lastName = ?, departmentId = ? where employeeid = ?";
				try {
					con = MySqldao.getCon();

					ps = con.prepareStatement(sql);
					ps.setInt(1, emp.getAge());
					ps.setString(2, emp.getFirstName());
					ps.setString(3, emp.getLastName());
					ps.setInt(4, emp.getDepartment().getDepartmentId());
					ps.setInt(5, emp.getEmployeeId());
					ps.execute();

					MySqldao.closePs(ps);
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					MySqldao.closePs(ps);
					MySqldao.closeCon(con);
				}

				break;
			}
		}
	}

	public void insert(Employee emp) {
		int lastId = 0;
		Iterator iter = employees.iterator();
		while (iter.hasNext()) {
			Employee temp = (Employee) iter.next();
			if (temp.getEmployeeId().intValue() > lastId) {
				lastId = temp.getEmployeeId().intValue();
			}
		}
		emp.setDepartment((Department) departmentsMap.get(emp.getDepartment()
				.getDepartmentId()));
		emp.setEmployeeId(new Integer(lastId + 1));
		employees.add(emp);

		Connection con = null;
		PreparedStatement ps = null;
		String sql = "insert into employee values (?, ?, ?, ?, ?)";
		try {
			con = MySqldao.getCon();

			ps = con.prepareStatement(sql);
			ps.setInt(1, emp.getEmployeeId());
			ps.setInt(2, emp.getAge());
			ps.setString(3, emp.getFirstName());
			ps.setString(4, emp.getLastName());
			ps.setInt(5, emp.getDepartment().getDepartmentId());
			ps.execute();

			MySqldao.closePs(ps);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MySqldao.closePs(ps);
			MySqldao.closeCon(con);
		}
	}

	public void delete(Integer id) {
		for (int i = 0; i < employees.size(); i++) {
			Employee tempEmp = (Employee) employees.get(i);
			if (tempEmp.getEmployeeId().equals(id)) {
				employees.remove(i);

				Connection con = null;
				PreparedStatement ps = null;
				String sql = "delete from employee where employeeid = ?";
				try {
					con = MySqldao.getCon();

					ps = con.prepareStatement(sql);
					ps.setInt(1, tempEmp.getEmployeeId());
					ps.execute();

					MySqldao.closePs(ps);
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					MySqldao.closePs(ps);
					MySqldao.closeCon(con);
				}
				break;
			}
		}
	}
}
